import pandas as pd
from mysql_in import mysql_sql_df
from mysql_in import mysql_to_sql
def dim_customer():
    # 显示所有列
    pd.set_option('display.max_columns', None)
    # 显示所有行
    # pd.set_option('display.max_rows', None)

    # 设置value的显示长度为100，默认为50
    pd.set_option('max_colwidth', 100)
    # 获取表信息
    df_customer = mysql_sql_df('customer')
    # 获取address表信息
    df_address = mysql_sql_df('address')
    # 列链接 左链接
    df_customer_01=pd.merge(left=df_customer,right=df_address,how='left',on='address_id')
    # 获取city表信息客户城市信息
    df_city=mysql_sql_df('city')
    # 列链接 左链接
    df_customer_01=pd.merge(left=df_customer_01,right=df_city,how='left',on='city_id')
    # 获取country表信息客户城市信息
    df_country=mysql_sql_df('country')
    # 修改’last_update的名字
    df_country['last_update_con']=df_country['last_update']
    df_country.drop(labels=['last_update'],axis=1,inplace=True)
    # 列链接 左链接
    df_customer_01=pd.merge(left=df_customer_01,right=df_country,how='left',on='country_id')
    # 转换active
    def zh(x):
        if x=='1':
            x='YES'
        elif x=='0':
            x='NO'
        else:
            x='No'
        return x
    df_customer_01['active']=df_customer_01['active'].map(zh)
    # 选着字段（删除会比选择麻烦）
    df_customer_01=df_customer_01[['customer_id','first_name','last_name','email','active','create_date','address','district','postal_code','phone','city','country','last_update']]

    # 写入mysql
    mysql_to_sql(df_customer_01,'dim_customer')